
create or replace function upd_timestamp() returns trigger as
$$
begin
    new.edit_time = current_timestamp;
return new;
end
$$
language plpgsql;


CREATE TABLE "compress_code" (
                                 "id" bigserial  NOT NULL,
                                 "compress_code" varchar(16) NOT NULL,
                                 "code_status" int2 NOT NULL DEFAULT 1,
                                 "sequence_value" varchar(32) NOT NULL,
                                 "strategy" varchar(8) NOT NULL DEFAULT 'sequence',
                                 "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                 "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                 "creator" varchar(32) NOT NULL DEFAULT '',
                                 "editor" varchar(32) NOT NULL DEFAULT '',
                                 "deleted" int2 NOT NULL DEFAULT 0,
                                 "version" int8 NOT NULL DEFAULT 1,
                                 PRIMARY KEY ("id")
);

create trigger t_name before update on "compress_code" for each row execute procedure upd_timestamp();

CREATE UNIQUE INDEX "uniq_compress_code.compress_code" ON "compress_code" USING btree (
    "compress_code" ASC
    );
COMMENT ON COLUMN "compress_code"."id" IS '主键';
COMMENT ON COLUMN "compress_code"."compress_code" IS '压缩码';
COMMENT ON COLUMN "compress_code"."code_status" IS '压缩码状态,1:未使用,2:已使用,3:已失效';
COMMENT ON COLUMN "compress_code"."sequence_value" IS '序列(盐)';
COMMENT ON COLUMN "compress_code"."strategy" IS '策略,sequence或者hash';
COMMENT ON COLUMN "compress_code"."create_time" IS '创建时间';
COMMENT ON COLUMN "compress_code"."edit_time" IS '更新时间';
COMMENT ON COLUMN "compress_code"."creator" IS '创建者';
COMMENT ON COLUMN "compress_code"."editor" IS '更新者';
COMMENT ON COLUMN "compress_code"."deleted" IS '软删除标识';
COMMENT ON COLUMN "compress_code"."version" IS '版本号';
COMMENT ON TABLE "compress_code" IS '压缩码';

CREATE TABLE "domain_conf" (
                               "id" bigserial NOT NULL,
                               "domain_value" varchar(16) NOT NULL,
                               "protocol" varchar(8) NOT NULL,
                               "domain_status" int2 NOT NULL DEFAULT 1,
                               "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                               "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                               "creator" varchar(32) NOT NULL DEFAULT '',
                               "editor" varchar(32) NOT NULL DEFAULT '',
                               "deleted" int2 NOT NULL DEFAULT 0,
                               "version" int8 NOT NULL DEFAULT 1,
                               PRIMARY KEY ("id")
);

create trigger t_name before update on "domain_conf" for each row execute procedure upd_timestamp();

CREATE UNIQUE INDEX "uniq_domain.domain_conf" ON "domain_conf" USING btree (
    "domain_value" ASC
    );
COMMENT ON COLUMN "domain_conf"."id" IS '主键';
COMMENT ON COLUMN "domain_conf"."domain_value" IS '域名';
COMMENT ON COLUMN "domain_conf"."protocol" IS '协议,https或者http';
COMMENT ON COLUMN "domain_conf"."domain_status" IS '域名状态,1:正常,2:已失效';
COMMENT ON COLUMN "domain_conf"."create_time" IS '创建时间';
COMMENT ON COLUMN "domain_conf"."edit_time" IS '更新时间';
COMMENT ON COLUMN "domain_conf"."creator" IS '创建者';
COMMENT ON COLUMN "domain_conf"."editor" IS '更新者';
COMMENT ON COLUMN "domain_conf"."deleted" IS '软删除标识';
COMMENT ON COLUMN "domain_conf"."version" IS '版本号';
COMMENT ON TABLE "domain_conf" IS '域名配置';

CREATE TABLE "transform_event_record" (
                                          "id" bigserial NOT NULL,
                                          "unique_identity" varchar(128) NOT NULL,
                                          "client_ip" varchar(64) NOT NULL,
                                          "short_url" varchar(32) NOT NULL,
                                          "long_url" varchar(768) NOT NULL,
                                          "short_url_digest" varchar(128) NOT NULL,
                                          "long_url_digest" varchar(128) NOT NULL,
                                          "compress_code" varchar(16) NOT NULL,
                                          "record_time" timestamp NOT NULL,
                                          "user_agent" varchar(2048),
                                          "cookie_value" varchar(2048),
                                          "query_param" varchar(2048),
                                          "province" varchar(32),
                                          "city" varchar(32),
                                          "phone_type" varchar(64),
                                          "browser_type" varchar(64),
                                          "browser_version" varchar(128),
                                          "os_type" varchar(32),
                                          "device_type" varchar(32),
                                          "os_version" varchar(32),
                                          "transform_status" int2 NOT NULL DEFAULT 0,
                                          "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                          "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                          "creator" varchar(32) NOT NULL DEFAULT '',
                                          "editor" varchar(32) NOT NULL DEFAULT '',
                                          "deleted" int2 NOT NULL DEFAULT 0,
                                          "version" int8 NOT NULL DEFAULT 1,
                                          PRIMARY KEY ("id")
);

create trigger t_name before update on "transform_event_record" for each row execute procedure upd_timestamp();

CREATE INDEX "idx_record_time.transform_event_record" ON "transform_event_record" USING btree (
    "record_time" ASC
    );
CREATE INDEX "idx_short_url_digest.transform_event_record" ON "transform_event_record" USING btree (
    "short_url_digest" ASC
    );
CREATE INDEX "idx_long_url_digest.transform_event_record" ON "transform_event_record" USING btree (
    "long_url_digest" ASC
    );
CREATE INDEX "idx_unique_identity.transform_event_record" ON "transform_event_record" USING btree (
    "unique_identity" ASC
    );
COMMENT ON COLUMN "transform_event_record"."id" IS '主键';
COMMENT ON COLUMN "transform_event_record"."unique_identity" IS '唯一身份标识,SHA-1(客户端IP-UA)';
COMMENT ON COLUMN "transform_event_record"."client_ip" IS '客户端IP';
COMMENT ON COLUMN "transform_event_record"."short_url" IS '短链URL';
COMMENT ON COLUMN "transform_event_record"."long_url" IS '长链URL';
COMMENT ON COLUMN "transform_event_record"."short_url_digest" IS '短链摘要';
COMMENT ON COLUMN "transform_event_record"."long_url_digest" IS '长链摘要';
COMMENT ON COLUMN "transform_event_record"."compress_code" IS '压缩码';
COMMENT ON COLUMN "transform_event_record"."record_time" IS '记录时间戳';
COMMENT ON COLUMN "transform_event_record"."user_agent" IS 'UA';
COMMENT ON COLUMN "transform_event_record"."cookie_value" IS 'cookie';
COMMENT ON COLUMN "transform_event_record"."query_param" IS 'URL参数';
COMMENT ON COLUMN "transform_event_record"."province" IS '省份';
COMMENT ON COLUMN "transform_event_record"."city" IS '城市';
COMMENT ON COLUMN "transform_event_record"."phone_type" IS '手机型号';
COMMENT ON COLUMN "transform_event_record"."browser_type" IS '浏览器类型';
COMMENT ON COLUMN "transform_event_record"."browser_version" IS '浏览器版本号';
COMMENT ON COLUMN "transform_event_record"."os_type" IS '操作系统型号';
COMMENT ON COLUMN "transform_event_record"."device_type" IS '设备型号';
COMMENT ON COLUMN "transform_event_record"."os_version" IS '操作系统版本号';
COMMENT ON COLUMN "transform_event_record"."transform_status" IS '转换状态,1:转换成功,2:转换失败,3:重定向成功,4:重定向失败';
COMMENT ON COLUMN "transform_event_record"."create_time" IS '创建时间';
COMMENT ON COLUMN "transform_event_record"."edit_time" IS '更新时间';
COMMENT ON COLUMN "transform_event_record"."creator" IS '创建者';
COMMENT ON COLUMN "transform_event_record"."editor" IS '更新者';
COMMENT ON COLUMN "transform_event_record"."deleted" IS '软删除标识';
COMMENT ON COLUMN "transform_event_record"."version" IS '版本号';
COMMENT ON TABLE "transform_event_record" IS '转换事件记录';

CREATE TABLE "url_map" (
                           "id" bigserial NOT NULL,
                           "short_url" varchar(32) NOT NULL,
                           "long_url" varchar(768) NOT NULL,
                           "short_url_digest" varchar(128) NOT NULL,
                           "long_url_digest" varchar(128) NOT NULL,
                           "compress_code" varchar(16) NOT NULL,
                           "description" varchar(64),
                           "source" varchar(32) NOT NULL,
                           "url_status" int2 NOT NULL DEFAULT 1,
                           "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                           "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                           "creator" varchar(32) NOT NULL DEFAULT '',
                           "editor" varchar(32) NOT NULL DEFAULT '',
                           "deleted" int2 NOT NULL DEFAULT 0,
                           "version" int8 NOT NULL DEFAULT 1,
                           PRIMARY KEY ("id")
);

create trigger t_name before update on "url_map" for each row execute procedure upd_timestamp();

CREATE UNIQUE INDEX "uniq_compress_code.url_map" ON "url_map" USING btree (
    "compress_code" ASC
    );
CREATE INDEX "idx_short_url.url_map" ON "url_map" USING btree (
    "short_url" ASC
    );
CREATE INDEX "idx_short_url_digest.url_map" ON "url_map" USING btree (
    "short_url_digest" ASC
    );
CREATE INDEX "idx_long_url_digest.url_map" ON "url_map" USING btree (
    "long_url_digest" ASC
    );
COMMENT ON COLUMN "url_map"."id" IS '主键';
COMMENT ON COLUMN "url_map"."short_url" IS '短链URL';
COMMENT ON COLUMN "url_map"."long_url" IS '长链URL';
COMMENT ON COLUMN "url_map"."short_url_digest" IS '短链摘要';
COMMENT ON COLUMN "url_map"."long_url_digest" IS '长链摘要';
COMMENT ON COLUMN "url_map"."compress_code" IS '压缩码';
COMMENT ON COLUMN "url_map"."description" IS '描述';
COMMENT ON COLUMN "url_map"."source" IS '来源';
COMMENT ON COLUMN "url_map"."url_status" IS 'URL状态,1:正常,2:已失效';
COMMENT ON COLUMN "url_map"."create_time" IS '创建时间';
COMMENT ON COLUMN "url_map"."edit_time" IS '更新时间';
COMMENT ON COLUMN "url_map"."creator" IS '创建者';
COMMENT ON COLUMN "url_map"."editor" IS '更新者';
COMMENT ON COLUMN "url_map"."deleted" IS '软删除标识';
COMMENT ON COLUMN "url_map"."version" IS '版本号';
COMMENT ON TABLE "url_map" IS 'URL映射表';

CREATE TABLE "url_map_replace_record" (
                                          "id" bigserial NOT NULL,
                                          "url_map_id" numeric(20) NOT NULL,
                                          "old_long_url" varchar(768) NOT NULL,
                                          "new_long_url" varchar(768) NOT NULL,
                                          "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                          "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                          "creator" varchar(32) NOT NULL DEFAULT '',
                                          "editor" varchar(32) NOT NULL DEFAULT '',
                                          "deleted" int2 NOT NULL DEFAULT 0,
                                          "version" int8 NOT NULL DEFAULT 1,
                                          PRIMARY KEY ("id")
);

create trigger t_name before update on "url_map_replace_record" for each row execute procedure upd_timestamp();

CREATE INDEX "idx_url_map_id.url_map_replace_record" ON "url_map_replace_record" USING btree (
    "url_map_id" ASC
    );
COMMENT ON COLUMN "url_map_replace_record"."id" IS '主键';
COMMENT ON COLUMN "url_map_replace_record"."url_map_id" IS 'URL映射ID';
COMMENT ON COLUMN "url_map_replace_record"."old_long_url" IS '旧长链URL';
COMMENT ON COLUMN "url_map_replace_record"."new_long_url" IS '新长链URL';
COMMENT ON COLUMN "url_map_replace_record"."create_time" IS '创建时间';
COMMENT ON COLUMN "url_map_replace_record"."edit_time" IS '更新时间';
COMMENT ON COLUMN "url_map_replace_record"."creator" IS '创建者';
COMMENT ON COLUMN "url_map_replace_record"."editor" IS '更新者';
COMMENT ON COLUMN "url_map_replace_record"."deleted" IS '软删除标识';
COMMENT ON COLUMN "url_map_replace_record"."version" IS '版本号';
COMMENT ON TABLE "url_map_replace_record" IS 'URL映射替换记录表';

CREATE TABLE "visit_statistics" (
                                    "id" bigserial NOT NULL,
                                    "statistics_date" date NOT NULL,
                                    "pv_count" numeric(20) NOT NULL DEFAULT 0,
                                    "uv_count" numeric(20) NOT NULL DEFAULT 0,
                                    "ip_count" numeric(20) NOT NULL DEFAULT 0,
                                    "effective_redirection_count" numeric(20) NOT NULL DEFAULT 0,
                                    "ineffective_redirection_count" numeric(20) NOT NULL DEFAULT 0,
                                    "compress_code" varchar(16) NOT NULL,
                                    "short_url_digest" varchar(128) NOT NULL,
                                    "long_url" varchar(768) NOT NULL,
                                    "long_url_digest" varchar(128) NOT NULL,
                                    "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                    "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                    "creator" varchar(32) NOT NULL DEFAULT '',
                                    "editor" varchar(32) NOT NULL DEFAULT '',
                                    "deleted" int2 NOT NULL DEFAULT 0,
                                    "version" int8 NOT NULL DEFAULT 1,
                                    PRIMARY KEY ("id")
);

create trigger t_name before update on "visit_statistics" for each row execute procedure upd_timestamp();

CREATE UNIQUE INDEX "uniq_date_code_digest.visit_statistics" ON "visit_statistics" USING btree (
    "statistics_date" ASC,
    "compress_code" ASC,
    "short_url_digest" ASC,
    "long_url_digest" ASC
    );
COMMENT ON COLUMN "visit_statistics"."id" IS '主键';
COMMENT ON COLUMN "visit_statistics"."statistics_date" IS '统计日期';
COMMENT ON COLUMN "visit_statistics"."pv_count" IS '页面流量数';
COMMENT ON COLUMN "visit_statistics"."uv_count" IS '独立访客数';
COMMENT ON COLUMN "visit_statistics"."ip_count" IS '独立IP数';
COMMENT ON COLUMN "visit_statistics"."effective_redirection_count" IS '有效跳转数';
COMMENT ON COLUMN "visit_statistics"."ineffective_redirection_count" IS '无效跳转数';
COMMENT ON COLUMN "visit_statistics"."compress_code" IS '压缩码';
COMMENT ON COLUMN "visit_statistics"."short_url_digest" IS '短链摘要';
COMMENT ON COLUMN "visit_statistics"."long_url_digest" IS '长链摘要';
COMMENT ON COLUMN "visit_statistics"."long_url" IS '长链URL';
COMMENT ON COLUMN "visit_statistics"."create_time" IS '创建时间';
COMMENT ON COLUMN "visit_statistics"."edit_time" IS '更新时间';
COMMENT ON COLUMN "visit_statistics"."creator" IS '创建者';
COMMENT ON COLUMN "visit_statistics"."editor" IS '更新者';
COMMENT ON COLUMN "visit_statistics"."deleted" IS '软删除标识';
COMMENT ON COLUMN "visit_statistics"."version" IS '版本号';
COMMENT ON TABLE "visit_statistics" IS '访问数据统计';

CREATE TABLE "user_info" (
                                    "id" bigserial NOT NULL,
                                    "user_name" varchar(32) NOT NULL,
                                    "password" varchar(32) NOT NULL ,
                                    "create_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                    "edit_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    PRIMARY KEY ("id")
);


CREATE UNIQUE INDEX "uniq_user_name.user_info" ON "user_info" USING btree (
    "user_name" ASC
    );
COMMENT ON COLUMN "user_info"."id" IS '主键';
COMMENT ON COLUMN "user_info"."user_name" IS '用户姓名';
COMMENT ON COLUMN "user_info"."password" IS '用户密码';
COMMENT ON COLUMN "user_info"."create_time" IS '创建时间';
COMMENT ON COLUMN "user_info"."edit_time" IS '更新时间';
COMMENT ON TABLE "user_info" IS '用户信息表';

CREATE TABLE "user_login_record" (
                             "id" bigserial NOT NULL,
                             "user_id" int8 NOT NULL,
                             "login_ip" varchar(128) NOT NULL ,
                             "login_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                            PRIMARY KEY ("id")
);


CREATE INDEX "idx_user_id.user_login_record" ON "user_login_record" USING btree (
    "user_id" ASC
    );
COMMENT ON COLUMN "user_login_record"."id" IS '主键';
COMMENT ON COLUMN "user_login_record"."user_id" IS '用户id';
COMMENT ON COLUMN "user_login_record"."login_ip" IS '登录ip';
COMMENT ON COLUMN "user_login_record"."login_time" IS '登录时间';
COMMENT ON TABLE "user_login_record" IS '用户登录记录表';

